package com.errol.generic.file.excel.service;

import com.renjia.service.generic.file.core.pojo.FileResolvedCode;
import com.renjia.service.generic.file.core.service.FileInnerService;
import com.renjia.service.generic.file.core.service.FileService;
import com.renjia.service.generic.file.excel.model.ExcelResolved;
import com.renjia.service.generic.file.excel.pojo.ExcelColumnDescriptor;
import com.renjia.service.generic.file.excel.pojo.ExcelConfig;
import com.renjia.service.generic.file.excel.pojo.ExcelValidator;
import com.renjia.service.generic.log.LogService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.lang.NonNull;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Service;

import javax.validation.Validation;
import javax.validation.Validator;
import java.io.File;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Created by Errol on 20/04/24.
 *
 * @author zhang xiao bin
 * @version v1.0
 * @date 2020/10/21
 * @since v4.7.6
 */
@Service
class ExcelInnerReaderServiceImpl {

    private final ExcelInnerServiceImpl excelInnerService;
    private final FileInnerService fileInnerService;
    private final FileService fileService;
    private final ExcelInnerWriterServiceImpl excelInnerWriterService;
    private final LogService logService;

    @Autowired
    public ExcelInnerReaderServiceImpl(ExcelInnerServiceImpl excelInnerService, FileInnerService fileInnerService, FileService fileService,
                                       ExcelInnerWriterServiceImpl excelInnerWriterService, LogService logService) {
        this.excelInnerService = excelInnerService;
        this.fileInnerService = fileInnerService;
        this.fileService = fileService;
        this.excelInnerWriterService = excelInnerWriterService;
        this.logService = logService;
    }

    /**
     * 读取 excel
     */
    <R> ExcelResolved<R> readFromExcel(@NonNull String from, @NonNull Class<R> clazz, @NonNull Map<String, Map<String, String>> dynamicSources, @Nullable ExcelValidator<R> excelValidator) throws Exception {
        excelInnerService.checkClassMapping(clazz);
        List<ExcelColumnDescriptor> columnDescriptors = excelInnerService.getColumnDescriptors(clazz, null);

        int sheetHeaderRowCount = excelInnerService.getSheetHeaderRowCount(columnDescriptors);
        ExcelResolved<R> excelResolved = checkFiles(from, clazz, columnDescriptors, sheetHeaderRowCount);
        if (excelResolved != null) {
            return excelResolved;
        }

        List<File> excels = fileService.getFiles(from);
        Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
        List<R> values = new ArrayList<>();
        List<Map<String, String>> errors = new ArrayList<>();
        Workbook workbook = null;
        for (File excel : excels) {
            try {
                workbook = WorkbookFactory.create(excel);
                if (workbook != null) {
                    Sheet sheet = workbook.getSheetAt(0);
                    resolveOneSheet(clazz, columnDescriptors, sheetHeaderRowCount, excel.getName(), sheet, values, errors, validator, dynamicSources, excelValidator);
//                    // TODO: 20/10/26 同系统原 excel 上传，只看第一张 sheet，如果要考虑多张 sheet，则验证错误 excel 需要对应 sheet 分别输出错误数据
//                    break;
                }
            } catch (Exception e) {
                logService.error(e, "excel 解析出错");
            } finally {
                if (workbook != null) {
                    workbook.close();
                }
            }
        }


        if (errors.size() > 0) {
            return new ExcelResolved<>(values, excelInnerWriterService.writeToExcel(errors, columnDescriptors, clazz));
        }
        return new ExcelResolved<>(values, null);
    }

    private <R> ExcelResolved<R> checkFiles(String from, Class<R> clazz, List<ExcelColumnDescriptor> columnDescriptors, int sheetHeaderRowCount) throws Exception {
        if (!Arrays.stream(from.split(",")).allMatch(s -> excelInnerService.getExcelExtensions().contains(fileService.getFileExtension(s)))) {
            return new ExcelResolved<>(FileResolvedCode.FILE_TYPE_ERROR);
        }
        List<File> excels = fileService.getFiles(from);
        if (!excels.stream().allMatch(File::exists)) {
            return new ExcelResolved<>(FileResolvedCode.MISSING_FILE);
        }
        ExcelConfig excelConfig = clazz.getAnnotation(ExcelConfig.class);
        Workbook workbook = null;
        try {
            for (File excel : excels) {
                workbook = WorkbookFactory.create(excel);
                if (workbook != null) {
                    Sheet sheet = workbook.getSheetAt(0);
                    ExcelResolved<R> excelResolved = checkSheetFormat(excel.getName(), excelConfig, sheet, columnDescriptors, sheetHeaderRowCount);
                    if (excelResolved != null) {
                        return excelResolved;
                    }
//                    // TODO: 20/10/26 同系统原 excel 上传，只看第一张 sheet，如果要考虑多张 sheet，则验证错误 excel 需要对应 sheet 分别输出错误数据
//                    break;
                }
                workbook.close();
            }
        } catch (Exception e) {
            logService.error(e, "检查excel出错");
        } finally {
            if (workbook != null) {
                workbook.close();
            }
        }
        return null;
    }

    private <R> ExcelResolved<R> checkSheetFormat(String excelName, ExcelConfig excelConfig, Sheet sheet, List<ExcelColumnDescriptor> columnDescriptors, int sheetHeaderRowCount) {
        String messagePrefix = excelName + " / " + sheet.getSheetName() + " / ";
        int r = "".equals(excelConfig.title()) ? 0 : 1;

        List<List<String>> header = new ArrayList<>();
        for (int i = 0; i < sheetHeaderRowCount; i++) {
            Row row = sheet.getRow(r);
            for (int j = 0; j < columnDescriptors.size(); j++) {
                if (i == 0) {
                    header.add(new ArrayList<>());
                }
                Cell cell = row.getCell(j);
                if (cell == null) {
                    return new ExcelResolved<>(FileResolvedCode.SHEET_FORMAT_ERROR, messagePrefix + "第" + (r + 1) + "行第" + (j + 1) + "列不能为空值");
                }
                if (cell.getCellType() == CellType.STRING ||
                        (cell.getCellType() == CellType.BLANK && (cell = getFirstCellOfRange(cell)).getCellType() == CellType.STRING)) {
                    header.get(j).add(cell.getStringCellValue());
                } else {
                    return new ExcelResolved<>(FileResolvedCode.SHEET_FORMAT_ERROR, messagePrefix + "列表头单元格内容必须是字符串");
                }
            }
            r++;
        }

        ExcelConfig.MappingPolicy mappingPolicy = excelConfig.mappingPolicy();
        if (mappingPolicy != ExcelConfig.MappingPolicy.ORDER) {
            for (int i = 0; i < header.size(); i++) {
                List<ExcelColumnDescriptor> descriptors = columnDescriptors.subList(i, columnDescriptors.size());
                for (int j = 0; j < sheetHeaderRowCount; j++) {
                    String head = header.get(i).get(j);
                    List<ExcelColumnDescriptor> temp = new ArrayList<>();
                    for (ExcelColumnDescriptor descriptor : descriptors) {
                        ExcelColumnDescriptor child = descriptor.getChildAt(j);
                        if (child == null && head.equals(header.get(i).get(j - 1))) {
                            temp.add(descriptor);
                        }
                        if (child != null && head.equals(mappingPolicy == ExcelConfig.MappingPolicy.TITLE ?
                                child.getExcelColConfig().title() : child.getPropertyDescriptor().getName())) {
                            temp.add(descriptor);
                        }
                    }
                    descriptors = temp;
                }
                if (descriptors.size() == 1) {
                    if (columnDescriptors.remove(descriptors.get(0))) {
                        columnDescriptors.add(i, descriptors.get(0));
                        continue;
                    }
                }
                return new ExcelResolved<>(FileResolvedCode.SHEET_FORMAT_ERROR, messagePrefix + "第 " + (i + 1) + " 列表头验证失败");
            }
        }
        return null;
    }

    private <R> void resolveOneSheet(Class<R> clazz, List<ExcelColumnDescriptor> columnDescriptors, int sheetHeaderRowCount, String excelName, Sheet sheet,
                                     List<R> values, List<Map<String, String>> errors, Validator validator, Map<String, Map<String, String>> dynamicSources,
                                     ExcelValidator<R> excelValidator) throws Exception {
        ExcelConfig excelConfig = clazz.getAnnotation(ExcelConfig.class);
        int r = "".equals(excelConfig.title()) ? sheetHeaderRowCount : sheetHeaderRowCount + 1;
        boolean trim = excelConfig.trim();
        while (r <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(r);
            r++;
            if (row == null) {
                continue;
            }
            R value = clazz.newInstance();
            Map<String, String> error = new HashMap<>();
            boolean valid = true;
            boolean emptyRow = true;
            List<Integer> errorColumn = new ArrayList<>();
            for (int j = 0; j < columnDescriptors.size(); j++) {
                Cell cell = row.getCell(j);
                ExcelColumnDescriptor descriptor = columnDescriptors.get(j);
                String v;
                if (cell == null) {
                    v = null;
                } else {
                    if (cell.getCellType() == CellType.BLANK) {
                        cell = getFirstCellOfRange(cell);
                    }
                    switch (cell.getCellType()) {
                        case STRING:
                        case BLANK:
                            v = cell.getStringCellValue();
                            break;
                        case NUMERIC:
                            try {
                                v = BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
                            } catch (Exception e) {
                                logService.error(e, "读取excel数字类型单元格内容异常");
                                v = null;
                            }
                            break;
                        default:
                            v = "数据类型错误";
                            valid = false;
                            errorColumn.add(j);
                            break;
                    }
                }
                if ("".equals(v) || " ".equals(v) || "null".equals(v)) {
                    v = null;
                } else {
                    emptyRow = false;
                }
                String msg = descriptor.invokeSetter(value, v, validator, dynamicSources, trim);
                if (msg != null) {
                    v += msg;
                    valid = false;
                    errorColumn.add(j);
                }
                error.put(String.valueOf(j), v);
            }
            if (valid && excelValidator != null) {
                String failMsg = excelValidator.reviewRowData(value);
                if (failMsg != null && failMsg.length() > 0) {
                    valid = false;
                    error.put("failMsg", failMsg);
                }
            }
            if (valid) {
                values.add(value);
            } else if (!emptyRow) {
                if (error.values().stream().anyMatch(s -> s != null && !s.startsWith("null"))) {
                    error.put("row", String.valueOf(r + 1));
                    error.put("cols", errorColumn.stream().map(String::valueOf).collect(Collectors.joining(",")));
                    error.put("sheet", sheet.getSheetName());
                    error.put("excel", excelName);
                    errors.add(error);
                }
            }
        }
    }

    private Cell getFirstCellOfRange(Cell cell) {
        CellRangeAddress range = getRangeOfCell(cell);
        if (range != null) {
            return cell.getSheet().getRow(range.getFirstRow()).getCell(range.getFirstColumn());
        }
        return cell;
    }

    private CellRangeAddress getRangeOfCell(Cell cell) {
        Sheet sheet = cell.getSheet();
        for (CellRangeAddress range : sheet.getMergedRegions()) {
            if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                return range;
            }
        }
        return null;
    }
}
